#!/usr/bin/env python3
# -*- coding: UTF-8 -*-
"""
@author: caijj
@version: V1.0
@file: zg_sz_new_house_info.py
@time: 2022/02/11
"""
import csv
import multiprocessing
import os
import random
import re
import uuid
from time import sleep
import pandas as pd
import requests
# from fake_useragent import UserAgent
from lxml import etree
from selenium import webdriver
from sqlalchemy import create_engine
from sqlalchemy.exc import ResourceClosedError
from common.globalvar import GlobalVar
from config.conf import cm
from tools import timeutil
from tools.DingTalk_Base import DingTalk_Disaster
from tools.database_tool import DataBaseTool
from tools.decorator import update_flag, send_ding_msg

ding = DingTalk_Disaster()
table_root_directory = 'mega_bk_{city}_xf_house_type_info'
file_root_directory = 'mega_bk_{city}_xf_house_type_info.csv'
estate_info_table_root_directory = 'mega_bk_{city}_xf_estate_info'
estate_info_file_root_directory = 'beike_{city}_xf.txt'


class GetNewHouseModelInfo:
    def __init__(self):
        self.city = GlobalVar.now_city

    @staticmethod
    def chrome_enter_target_page(url='https://su.xinfang.zhuge.com/'):  # chrome进入目标页面
        chrome_options = webdriver.ChromeOptions()
        chrome_options.add_argument('--headless')
        chrome_options.add_argument('--disable-gpu')
        web_driver = webdriver.Chrome(options=chrome_options)
        web_driver.maximize_window()
        web_driver.get(url)
        return web_driver

    @staticmethod
    def quit(web_driver):  # 退出
        if web_driver is not None:
            web_driver.quit()
            sleep(5)

    def get_new_house_model(self, create_time, house_detail_list, table_name, file_name, count=0):
        web_driver = None
        try:
            for house_detail in house_detail_list[count:]:
                model_info_list = []
                estate_id = house_detail[0]
                resblock_name = house_detail[1]
                detail_link = house_detail[2]
                web_driver = self.chrome_enter_target_page(detail_link)
                html_source = web_driver.page_source
                if html_source == '<html><head></head><body></body></html>':
                    print('网络异常')
                    self.quit(web_driver)
                    return self.get_new_house_model(create_time, house_detail_list, table_name, file_name, count)
                elif '访问频次过高或周边同事/邻居对贝壳房源存在恶意爬取行为' in html_source:
                    print('对贝壳房源存在恶意爬取行为需手动验证' + str(timeutil.dt_strptime("%Y-%m-%d %H%M%S")))
                    self.quit(web_driver)
                    return self.get_new_house_model(create_time, house_detail_list, table_name, file_name, count)
                tree = etree.HTML(html_source)
                model_info_parent_ele_list = tree.xpath("//div[@class='modal-content']//li[@class='item top-item "
                                                        "animation'][1]/ul")
                if len(model_info_parent_ele_list) > 1:  # 当前房源存在户型
                    for model_info_parent_ele in model_info_parent_ele_list:
                        model_id = uuid.uuid4()
                        index = model_info_parent_ele_list.index(model_info_parent_ele)
                        img_src = tree.xpath(
                            "//div[@class='modal-content']//li[@class='item top-item animation'][1]/ul[" + str(
                                index + 1) + "]/img/@src")[0]
                        house_type_detail = \
                            tree.xpath("//div[@class='modal-content']//li[@class='item top-item animation']["
                                       "1]/ul[" + str(index + 1) + "]/div[@class='card-content']/div["
                                                                   "@class='content-title']")[0].text.strip()
                        house_type_status = \
                            tree.xpath("//div[@class='modal-content']//li[@class='item top-item animation']["
                                       "1]/ul[" + str(index + 1) + "]/div[@class='card-content']/div["
                                                                   "@class='content-title']/span")[0].text
                        house_area_text = \
                            tree.xpath("//div[@class='modal-content']//li[@class='item top-item animation']["
                                       "1]/ul[" + str(index + 1) + "]/div[@class='card-content']/div["
                                                                   "@class='content-area']")[0].text
                        building_area = house_area_text.split(' ')[0] + house_area_text.split(' ')[1]
                        orientation = house_area_text.split(' ')[2][1:-1]
                        price_ele = tree.xpath(
                            "//div[@class='modal-content']//li[@class='item top-item animation'][1]/ul"
                            "[" + str(index + 1) + "]/div[@class='card-content']/div["
                                                   "@class='content-price']/span")
                        if len(price_ele) == 0:
                            price = '价格待定'
                        else:
                            price = \
                                tree.xpath("//div[@class='modal-content']//li[@class='item top-item animation'][1]/ul"
                                           "[" + str(index + 1) + "]/div[@class='card-content']/div["
                                                                  "@class='content-price']/span")[0].text
                        house_type = house_type_detail[0:1]
                        model_info_list.append(
                            [model_id, estate_id, house_type, create_time, house_type_detail, img_src,
                             house_type_status, building_area, orientation, price, resblock_name] + house_detail[3:7])

                else:  # 不存在户型
                    pass
                df = pd.DataFrame(data=model_info_list,
                                  columns=['id', 'resblock_id', 'house_type', 'create_time', 'house_type_detail',
                                           'img_src', 'house_type_status', 'building_area', 'orientation', 'price',
                                           'resblock_name', 'resblock_status', 'resblock_type', 'region', 'district'])
                # 保存为csv格式
                df.to_csv(file_name, mode="a",
                          # columns=['model_id', '序号', '楼盘名称', '单价(元/㎡)', 'create_time', 'area', 'trend'],
                          encoding="utf-8",
                          header=False,
                          index=False)
                count += 1
                self.quit(web_driver)
        except Exception as e:
            # print(e)
            self.quit(web_driver)
            print('未知异常 + 户型链接{detail_link} + {time}'.format(detail_link=detail_link,
                                                             time=str(timeutil.dt_strptime("%Y-%m-%d %H%M%S"))))
            # sleep(10)
            return self.get_new_house_model(create_time, house_detail_list, table_name, file_name, count)

    @staticmethod
    def get_house_detail(table_name='mega_bk_sz_xf_estate_info'):  # 获取贝壳新房信息
        sql = "select id,detail_url from {}".format(table_name)
        db = DataBaseTool()
        result = db.select_sql(sql)
        return result

    @staticmethod
    def get_estate_info_from_txt(csv_name):  # 获取贝壳新房信息
        df = pd.read_csv(csv_name, sep=',', header=None)
        df.columns = [
            'id', 'resblock_name', 'detail_url', 'resblock_status',
            'resblock_type', 'region', 'district', 'position_info', 'house_type',
            'house_size', 'unit_price', 'total_price', 'tag'
        ]
        df.drop_duplicates(subset=['detail_url'], keep='first', inplace=True)
        return df.values.tolist()

    @staticmethod
    @update_flag()
    def insert_new_house_model_data(csv_name, table_name):
        engine = create_engine('mssql+pymssql://base:base@192.168.106.21:1433/JRZF_BASE?charset=utf8')
        # 读取本地CSV文件
        if not os.path.exists(csv_name):
            raise FileNotFoundError("配置文件%s不存在！" % csv_name)
        else:
            # pd.merge(df1, df11, on=['resblock_id','price'], how='outer', suffixes=('_up', '_down'))  # 合并df
            # df["number_1"].fillna(df["number_2"], inplace=True)  # 用某一列的值填充另一列的空值
            sql_result = DataBaseTool().get_column_name_sql(table_name)
            column_name_list = [column_item[0] for column_item in sql_result]
            df = pd.read_csv(csv_name, sep=',', header=None)
            df.columns = column_name_list
            # df.columns = ['id', 'resblock_id', 'house_type', 'create_time', 'house_type_detail', 'img_src',
            #               'house_type_status', 'building_area', 'orientation', 'price', 'resblock_name',
            #               'resblock_status', 'resblock_type', 'region', 'district']
            # 将新建的DataFrame储存为MySQL中的数据表，不储存index列
            try:
                df.to_sql(table_name, engine, index=False, if_exists='fail')
            except ValueError as e:
                try:
                    pd.read_sql_query("delete from {}".format(table_name), con=engine)
                    # print('进入删除数据逻辑')
                except ResourceClosedError as e:
                    # print('进入插入数据逻辑')
                    df.to_sql(table_name, engine, index=False, if_exists='append')
            # try:
            #     df.to_sql(table_name, engine, index=False, if_exists='fail')
            # except ValueError as e:
            #     df.to_sql(table_name, engine, index=False, if_exists='append')

    @staticmethod
    def del_data(table_name):
        db = DataBaseTool()
        sql = "delete from {}".format(table_name)
        db.update_sql(sql)

    @staticmethod
    def check_file_is_exists(csv_name):
        if os.path.isfile(csv_name):
            os.remove(csv_name)

    @send_ding_msg()
    def save_new_house_model_data(self):
        city_dict = GlobalVar.city_env
        for city in city_dict.keys():
            if city == 'sz':
                new_city = 'su'
            else:
                new_city = city
            GlobalVar.now_city = city
            time = timeutil.dt_strptime("%Y-%m-%d %H%M%S")

            estate_model_info_table_name = table_root_directory.format(city=city)
            estate_model_info_file_name = file_root_directory.format(city=city)
            estate_info_file_name = estate_info_file_root_directory.format(city=new_city)

            data_list = self.get_estate_info_from_txt(cm.tmp_file_path(estate_info_file_name))  # 获取楼盘信息

            GetNewHouseModelInfo().check_file_is_exists(cm.tmp_file_path(estate_model_info_file_name))
            self.get_new_house_model(time, data_list, estate_model_info_table_name,
                                     cm.tmp_file_path(estate_model_info_file_name))

            # self.insert_new_house_model_data(cm.tmp_file_path(estate_model_info_file_name),
            #                                  estate_model_info_table_name)l


if __name__ == '__main__':
    # GetNewHouseModelInfo().save_new_house_model_data()
    # 读取本地CSV文件
    csv1 = cm.tmp_file_path('test1.csv')
    csv2 = cm.tmp_file_path('test2.csv')
    df1 = pd.read_csv(csv1, sep=',', header=None)
    df2 = pd.read_csv(csv2, sep=',', header=None)
    df1.columns = ['id', 'resblock_id', 'data_id', 'house_type', 'house_type_status', 'building_area']
    df2.columns = ['id', 'resblock_id', 'data_id', 'house_type', 'house_type_status', 'building_area']
    merge_pd = pd.merge(df1, df2, on=['data_id'], how='outer', suffixes=('_up', '_down'))  # 合并df
    df_type = type(merge_pd)
    merge_pd["house_type_up"].fillna(merge_pd["house_type_down"], inplace=True)  # 用某一列的值填充另一列的空值
    merge_pd["house_type_down"].fillna(merge_pd["house_type_up"], inplace=True)  # 用某一列的值填充另一列的空值
    print(merge_pd)
